package com.genius.unite_platform.hrm.service.impl;
import java.io.OutputStream;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import org.springframework.stereotype.Service;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.genius.adminmanager.other.entity.FillAddJson;
import com.genius.adminmanager.other.entity.RequestJson;
import com.genius.adminmanager.other.entity.ResponseJson;
import com.genius.adminmanager.other.entity.SessionJson;
import com.genius.adminmanager.user.entity.UserJson;
import com.genius.dingding.util.FileUtils;
import com.genius.workflow.config.DefaultConfiguration;
import com.genius.workflow.element.SQLBean;
import com.genius.workflow.page.PageResult;
import com.genius.workflow.page.SearchResult;
import com.genius.workflow.page.SqlLang;
import com.genius.workflow.util.ConnectionException;
import com.genius.workflow.util.DBClosedException;
import com.genius.workflow.util.DBTools;
import com.genius.workflow.util.DBTools2;
import com.genius.workflow.util.DBUtils;
import com.genius.workflow.util.ExportExcel;

import com.genius.unite_platform.dict_info.entity.Dict_infoEntity;
import com.genius.unite_platform.hrm.service.Hrm_jhServiceAdapter;
import com.genius.unite_platform.meeting_info_detail.entity.Meeting_info_detailEntity;

@Service("hrm_jhSelectService")
public class Hrm_jhSelectServiceImpl extends Hrm_jhServiceAdapter{
	@Override
	public JSONObject import_dudao(JSONObject json, JSONObject response,HttpSession session) {    	
    	try {
    	//	DBTools2.delete("dManageDate", "hrm_jh_dudao", json);
    		DBTools2.deleteBach("dManageDate", "hrm_jh_dudao", json.getJSONArray("Dtl"));
    		JSONObject userJson = (JSONObject)session.getAttribute(SessionJson.currentUser);
			String insertField = "dManageDate,shopCode,shopName,branch,startDate,endDate,sOperCenterNO,sOperCenter,sDirectorNO,sDirectorName,sZoneMagNO,sZoneMagName,sEmployeeNO,sEmployeeName,lrr,lrsj=now()";
			String insertTable = "hrm_jh_dudao";
			
			DBTools2.insertBatch(insertField, insertTable,json.getJSONArray("Dtl"));
			response.put(ResponseJson.status, ResponseJson.status_Success);
			response.put(ResponseJson.msg, ResponseJson.msg_Import_Data_Success);
		}  catch (ConnectionException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
    		response.put(ResponseJson.msg, ResponseJson.msg_No_Porwer_Query_Field);
		} catch (DBClosedException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
    		response.put(ResponseJson.msg, ResponseJson.msg_DB_Closed_Failed);
		} catch (SQLException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Import_Data_Success);
		}
		return response;
	}
	@Override
	public JSONObject get_dudao(JSONObject json, JSONObject response) {
		int limit = json.getIntValue(PageResult.limit);
        int page = json.getIntValue(PageResult.page);
		SqlLang sql = null;
		String shopcodeString="";
		String shopnameString="";
		String sEmployeeNameString="";
		String sZoneMagNameString="";
		String sDirectorNameString="";
		if( json.getString("sDirectorName") != null && !json.getString("sDirectorName").equals("") ){
			sDirectorNameString = " and sDirectorName like '%"+json.getString("sDirectorName")+"%'";
		}
		if( json.getString("sZoneMagName") != null && !json.getString("sZoneMagName").equals("") ){
			sZoneMagNameString = " and sZoneMagName like '%"+json.getString("sZoneMagName")+"%'";
		}
		if( json.getString("sEmployeeName") != null && !json.getString("sEmployeeName").equals("") ){
			sEmployeeNameString = " and sEmployeeName like '%"+json.getString("sEmployeeName")+"%'";
		}
		if( json.getString("shopname") != null && !json.getString("shopname").equals("") ){
			shopnameString = " and shopName like '%"+json.getString("shopname")+"%'";
		}
		if( json.getString("shopcode") != null && !json.getString("shopcode").equals("") ){
			shopcodeString = " and shopCode = '"+json.getString("shopcode")+"'";
		}
        String selectSB = "select * from hrm_jh_dudao where 1=1 and dManageDate  = '"+json.getString("whereDate")+"' "+shopcodeString+shopnameString+sEmployeeNameString+sZoneMagNameString+sDirectorNameString;
        String headField = "*";
		//参数的json
		JSONObject parmeterJson = DBTools2.getWhereFieldByParameter(json);
		//普通查询的条件及字段值json
		SearchResult  searchResult = new SearchResult();//DBTools2.getWhereFieldBySearch(json);
		//合并所有的结果值统一到一个json中
		JSONObject mergeJson = new JSONObject();//DBTools2.mergeAll(parmeterJson, searchResult);
		String asc = null;
		String ascField = null;
		sql = DBTools2.getSelectPageSql(headField,selectSB,page,limit,ascField,asc,searchResult.getSearchWhere());
        try {
			String parameterArr [] = DBTools2.parsePermeterBySql(sql.getSourceSql(), mergeJson);
			PageResult result = DBTools2.selectByCount(sql,parameterArr);
			JSONArray data = (JSONArray)result.getData();
			response.put(ResponseJson.count, result.getCount());
			response.put(ResponseJson.data, data);
			response.put(ResponseJson.status, ResponseJson.status_Success);
        } catch (ConnectionException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_DB_Connect_Failed);
        } catch (DBClosedException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
            response.put(ResponseJson.msg, ResponseJson.msg_DB_Closed_Failed);
        } catch (SQLException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Query_Data_Error);
        }
		return response;
	}
	@Override
	public JSONObject import_fendian(JSONObject json, JSONObject response,HttpSession session) {    	
    	try {
    		DBTools2.deleteBach("targetDate", "hrm_jh_fendian", json.getJSONArray("Dtl"));
    		JSONObject userJson = (JSONObject)session.getAttribute(SessionJson.currentUser);
			String insertField = "targetDate,shopCode,shopName,minimum_sale,reasonable_sale,challenge_sale,minimum_gpm,reasonable_gpm,challenge_gpm,lrr,lrsj=now()";
			String insertTable = "hrm_jh_fendian";
			DBTools2.insertBatch(insertField, insertTable,json.getJSONArray("Dtl"));
			response.put(ResponseJson.status, ResponseJson.status_Success);
			response.put(ResponseJson.msg, ResponseJson.msg_Import_Data_Success);
		}  catch (ConnectionException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
    		response.put(ResponseJson.msg, ResponseJson.msg_No_Porwer_Query_Field);
		} catch (DBClosedException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
    		response.put(ResponseJson.msg, ResponseJson.msg_DB_Closed_Failed);
		} catch (SQLException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Import_Data_Success);
		}
		return response;
	}
	@Override
	public JSONObject get_fendian(JSONObject json, JSONObject response) {
		int limit = json.getIntValue(PageResult.limit);
        int page = json.getIntValue(PageResult.page);
		SqlLang sql = null;
		String shopcodeString="";
		if( json.getString("shopcode") != null && !json.getString("shopcode").equals("") ){
			shopcodeString = " and shopCode = '"+json.getString("shopcode")+"'";
		}
        String selectSB = "select * from hrm_jh_fendian where 1=1 and targetDate  = '"+json.getString("whereDate")+"' "+shopcodeString;
        String headField = "*";
		//参数的json
		JSONObject parmeterJson = DBTools2.getWhereFieldByParameter(json);
		//普通查询的条件及字段值json
		SearchResult  searchResult = new SearchResult();//DBTools2.getWhereFieldBySearch(json);
		//合并所有的结果值统一到一个json中
		JSONObject mergeJson = new JSONObject();//DBTools2.mergeAll(parmeterJson, searchResult);
		String asc = null;
		String ascField = null;
		sql = DBTools2.getSelectPageSql(headField,selectSB,page,limit,ascField,asc,searchResult.getSearchWhere());
        try {
			String parameterArr [] = DBTools2.parsePermeterBySql(sql.getSourceSql(), mergeJson);
			PageResult result = DBTools2.selectByCount(sql,parameterArr);
			JSONArray data = (JSONArray)result.getData();
			response.put(ResponseJson.count, result.getCount());
			response.put(ResponseJson.data, data);
			response.put(ResponseJson.status, ResponseJson.status_Success);
        } catch (ConnectionException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_DB_Connect_Failed);
        } catch (DBClosedException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
            response.put(ResponseJson.msg, ResponseJson.msg_DB_Closed_Failed);
        } catch (SQLException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Query_Data_Error);
        }
		return response;
	}
	@Override
	public JSONObject import_zongjian(JSONObject json, JSONObject response,HttpSession session) {    	
    	try {
    		DBTools2.deleteBach("targetDate", "hrm_jh_zongjian", json.getJSONArray("Dtl"));
    		JSONObject userJson = (JSONObject)session.getAttribute(SessionJson.currentUser);
			String insertField = "targetDate,sDirectorNO,sDirectorName,minimum_sale,reasonable_sale,challenge_sale,minimum_gpm,reasonable_gpm,challenge_gpm,lrr,lrsj=now()";
			String insertTable = "hrm_jh_zongjian";
			DBTools2.insertBatch(insertField, insertTable,json.getJSONArray("Dtl"));
			response.put(ResponseJson.status, ResponseJson.status_Success);
			response.put(ResponseJson.msg, ResponseJson.msg_Import_Data_Success);
		}  catch (ConnectionException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
    		response.put(ResponseJson.msg, ResponseJson.msg_No_Porwer_Query_Field);
		} catch (DBClosedException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
    		response.put(ResponseJson.msg, ResponseJson.msg_DB_Closed_Failed);
		} catch (SQLException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Import_Data_Success);
		}
		return response;
	}
	@Override
	public JSONObject get_zongjian(JSONObject json, JSONObject response) {
		int limit = json.getIntValue(PageResult.limit);
        int page = json.getIntValue(PageResult.page);
		SqlLang sql = null;
		String sDirectorNameString="";
		if( json.getString("sDirectorName") != null && !json.getString("sDirectorName").equals("") ){
			sDirectorNameString = " and sDirectorName like '"+json.getString("sDirectorName")+"%'";
		}
        String selectSB = "select * from hrm_jh_zongjian where 1=1 and targetDate  = '"+json.getString("whereDate")+"' "+sDirectorNameString;
        String headField = "*";
		//参数的json
		JSONObject parmeterJson = DBTools2.getWhereFieldByParameter(json);
		//普通查询的条件及字段值json
		SearchResult  searchResult = new SearchResult();//DBTools2.getWhereFieldBySearch(json);
		//合并所有的结果值统一到一个json中
		JSONObject mergeJson = new JSONObject();//DBTools2.mergeAll(parmeterJson, searchResult);
		String asc = null;
		String ascField = null;
		sql = DBTools2.getSelectPageSql(headField,selectSB,page,limit,ascField,asc,searchResult.getSearchWhere());
        try {
			String parameterArr [] = DBTools2.parsePermeterBySql(sql.getSourceSql(), mergeJson);
			PageResult result = DBTools2.selectByCount(sql,parameterArr);
			JSONArray data = (JSONArray)result.getData();
			response.put(ResponseJson.count, result.getCount());
			response.put(ResponseJson.data, data);
			response.put(ResponseJson.status, ResponseJson.status_Success);
        } catch (ConnectionException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_DB_Connect_Failed);
        } catch (DBClosedException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
            response.put(ResponseJson.msg, ResponseJson.msg_DB_Closed_Failed);
        } catch (SQLException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Query_Data_Error);
        }
		return response;
	}
	@Override
	public JSONObject fillAdd(JSONObject json, JSONObject response) {   
	try {
    		JSONObject addJson = new JSONObject();
    		//select CONCAT(DATE_FORMAT(CURDATE(),'%Y%m%d'),IFNULL(LPAD(RIGHT(max(mno),6)+1,6,0),'000001'))mno from meeting_info where mno >= DATE_FORMAT(CURDATE(),'%Y%m%d0001') and mno <= DATE_FORMAT(CURDATE(),'%Y%m%d9999')
    		String sql = "select CONCAT(DATE_FORMAT(CURDATE(),'%Y%m%d'),IFNULL(LPAD(RIGHT(max(NO),4)+1,4,0),'0001'))NO from hrm_jh_performance where NO like concat(DATE_FORMAT(CURDATE(),'%Y%m%d'),'%')";
			HashMap<String, String> sqlMap = new HashMap<>();
			sqlMap.put("val", "select distinct sOperCenterNO as  value1,sOperCenter as key1 from hrm_jh_dudao order by value1  ");
			JSONObject fillJson = new JSONObject();
			addJson = DBUtils.select(sql);
			JSONObject fillEditJson = DBTools.getFilling(sqlMap);
			fillJson.put(FillAddJson.field, addJson);
			fillJson.put(FillAddJson.select, fillEditJson);
			
			response.put(ResponseJson.status, ResponseJson.status_Success);
			response.put(ResponseJson.msg, ResponseJson.msg_Add_Data_Success);
			response.put(ResponseJson.data,fillJson);
		}  catch (ConnectionException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
    		response.put(ResponseJson.msg, ResponseJson.msg_No_Porwer_Query_Field);
		} catch (DBClosedException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
    		response.put(ResponseJson.msg, ResponseJson.msg_DB_Closed_Failed);
		} catch (SQLException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Add_Fill_Data_Error);
		}
		return response;
	}
	@Override
	public JSONObject fillEdit(JSONObject json, JSONObject response) {   
	try {
    		JSONObject addJson = new JSONObject();
    		String selectField = "NO,name,date,type,val";
    		String whereField = "NO";
    		String table = "hrm_jh_performance";
			HashMap<String, String> sqlMap = new HashMap<>();
			sqlMap.put("val", "select distinct sOperCenterNO as  value1,sOperCenter as key1 from hrm_jh_dudao order by value1  ");
			JSONObject fillJson = new JSONObject();
			addJson = DBTools.getFilling(selectField,whereField,table,json,addJson);
			JSONObject fillEditJson = DBTools.getFilling(sqlMap);
			fillJson.put(FillAddJson.field, addJson);
			fillJson.put(FillAddJson.select, fillEditJson);
			response.put(ResponseJson.status, ResponseJson.status_Success);
			response.put(ResponseJson.msg, ResponseJson.msg_Add_Data_Success);
			response.put(ResponseJson.data,fillJson);
		}  catch (ConnectionException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
    		response.put(ResponseJson.msg, ResponseJson.msg_No_Porwer_Query_Field);
		} catch (DBClosedException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
    		response.put(ResponseJson.msg, ResponseJson.msg_DB_Closed_Failed);
		} catch (SQLException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Edit_Fill_Data_Error);
		}
		return response;
	}
	@Override
	public JSONObject getSelectBody(JSONObject json, JSONObject response) {
		int limit = json.getIntValue(PageResult.limit);
        int page = json.getIntValue(PageResult.page);
		SqlLang sql = null;
		String searString="";
		String qzString="";
		if(json.getString("val")!= null && !json.getString("val").equals("")) {
			searString = " and sOperCenterNO = '"+json.getString("val")+"'";
		}
        String selectSB = 
        		"SELECT endDate," + 
        		"shopCode,shopName,branch,sOperCenterNO,sOperCenter,sDirectorNO,sDirectorName,sZoneMagNO,sZoneMagName,sEmployeeNO,sEmployeeName,days, " + 
        		"totalsalemoney,totalinmoney,GPM, " + 
        		"ROUND(minimum_sale,2)minimum_sale,ROUND(reasonable_sale,2)reasonable_sale,ROUND(challenge_sale,2)challenge_sale,AVG, " + 
        		"ROUND(minimum_sale,2)sD_minimum_sale,ROUND(reasonable_sale,2)sD_reasonable_sale,ROUND(challenge_sale,2)sD_challenge_sale, " + 
        		"ROUND(IFNULL(AVG/minimum_sale,1),4)*100 as minimum_percent,ROUND(IFNULL(AVG/reasonable_sale,0),4)*100 as reasonable_percent,ROUND(IFNULL(AVG/challenge_sale,0),4)*100 as challenge_percent, " + 
        		"ROUND(IFNULL(totalsalemoney/minimum_sale,1),4)*100 as sD_minimum_percent,ROUND(IFNULL(totalsalemoney/reasonable_sale,1),4)*100 as sD_reasonable_percent,ROUND(IFNULL(totalsalemoney/challenge_sale,1),4)*100 as sD_challenge_percent, " + 
        		"ROUND(IFNULL(minimum_gpm,0),2)minimum_gpm,ROUND(IFNULL(reasonable_gpm,0),2)reasonable_gpm,ROUND(IFNULL(challenge_gpm,0),2)challenge_gpm, " + 
        		"ROUND(IFNULL(GPM_percent,0),2)GPM_percent, " + 
        		"ROUND(IFNULL(GPM_percent/minimum_gpm,1),4)*100 as minimum_gpm_percent,ROUND(IFNULL(GPM_percent/reasonable_gpm,1),4)*100 as reasonable_gpm_percent,ROUND(IFNULL(GPM_percent/challenge_gpm,1),4)*100 as challenge_gpm_percent  " + 
        		"from  " + 
        		"( " + 
        		"select TABa.endDate,TABa.shopCode,TABa.shopName,TABa.branch,sOperCenterNO,sOperCenter,TABa.sDirectorNO,sDirectorName,TABa.sZoneMagNO,sZoneMagName,TABa.sEmployeeNO,sEmployeeName,SUM(days)days, " + 
        		"SUM(totalsalemoney)totalsalemoney,SUM(totalinmoney)totalinmoney,SUM(GPM)GPM,SUM( GPM ) / SUM( totalsalemoney ) * 100 AS GPM_percent , " + 
        		"SUM(CASE WHEN '"+json.getString("type")+"' = 'shopCode' THEN TABc.minimum_sale/TABf.shopsum WHEN '"+json.getString("type")+"' = 'sZoneMagNO' THEN TABc.minimum_sale*days ELSE IF(TABa.branch='0',TABc.minimum_sale * days,ROUND( TABd.minimum_sale / num, 0 )) END )minimum_sale,"+
        		"SUM(CASE WHEN '"+json.getString("type")+"' = 'shopCode' THEN TABc.reasonable_sale/TABf.shopsum WHEN '"+json.getString("type")+"' = 'sZoneMagNO' THEN TABc.reasonable_sale*days ELSE IF(TABa.branch='0',TABc.reasonable_sale * days,ROUND( TABd.reasonable_sale / num, 0 )) END )reasonable_sale,"+
        		"SUM(CASE WHEN '"+json.getString("type")+"' = 'shopCode' THEN TABc.challenge_sale/TABf.shopsum WHEN '"+json.getString("type")+"' = 'sZoneMagNO' THEN TABc.challenge_sale*days ELSE IF(TABa.branch='0',TABc.challenge_sale * days,ROUND( TABd.challenge_sale / num, 0 )) END )challenge_sale,"+
        		"AVG, " + 
        		"(CASE WHEN '"+json.getString("type")+"' = 'shopCode' THEN TABc.minimum_gpm WHEN '"+json.getString("type")+"' = 'sZoneMagNO' THEN ROUND(SUM(TABc.minimum_sale*TABc.minimum_gpm*0.01*days)/SUM(TABc.minimum_sale*days),4)*100 ELSE IF(TABa.branch='0',SUM(TABc.minimum_sale*TABc.minimum_gpm*0.01*days)/SUM(TABc.minimum_sale*days)*100,TABd.minimum_gpm) END)minimum_gpm,"+
        		"(CASE WHEN '"+json.getString("type")+"' = 'shopCode' THEN TABc.reasonable_gpm WHEN '"+json.getString("type")+"' = 'sZoneMagNO' THEN ROUND(SUM(TABc.reasonable_sale*TABc.reasonable_gpm*0.01*days)/SUM(TABc.reasonable_sale*days),4)*100 ELSE IF(TABa.branch='0',SUM(TABc.reasonable_sale*TABc.reasonable_gpm*0.01*days)/SUM(TABc.reasonable_sale*days)*100,TABd.reasonable_gpm)  END)reasonable_gpm,"+
        		"(CASE WHEN '"+json.getString("type")+"' = 'shopCode' THEN TABc.challenge_gpm WHEN '"+json.getString("type")+"' = 'sZoneMagNO' THEN ROUND(SUM(TABc.challenge_sale*TABc.challenge_gpm*0.01*days)/SUM(TABc.challenge_sale*days),4)*100 ELSE IF(TABa.branch='0',SUM(TABc.challenge_sale*TABc.challenge_gpm*0.01*days)/SUM(TABc.challenge_sale*days)*100,TABd.challenge_gpm)  END)challenge_gpm " + 
        		"from " + 
        		"( " + 
        		"(SELECT endDate,shopCode,shopName,branch,sOperCenterNO,sOperCenter,sDirectorNO,sDirectorName,sZoneMagNO,sZoneMagName,sEmployeeNO,sEmployeeName from hrm_jh_dudao where dManageDate = '"+json.getString("date")+"')TABa  " + 
        		"LEFT JOIN " + 
        		"(SELECT b.shopCode,b.endDate,PERIOD_DIFF(b.endDate,b.startDate)+1 days,SUM( totalsalemoney ) totalsalemoney,SUM( totalinmoney ) totalinmoney,SUM( GPM ) GPM,ROUND( AVG( totalsalemoney ), 2 ) AVG," + 
        		"SUM( GPM ) / SUM( totalsalemoney ) * 100 AS GPM_percent FROM hrm_jh_dudao b left join " + 
        		"(select  * from jhcvs_index.sale_shop_gm where updateTime >= '"+json.getString("date")+"-01' and updateTime <= '"+json.getString("date")+"-31') a on b.shopCode = a.shopCode WHERE " + 
        		" dManageDate = '"+json.getString("date")+"' and updateTime >= b.startDate AND updateTime <=  b.endDate GROUP BY shopcode ,endDate)TABb ON TABa.shopcode = TABb.shopcode and TABa.endDate = TABb.endDate " + 
        		"LEFT JOIN " + 
        		"(SELECT shopcode,shopName,minimum_sale,reasonable_sale,challenge_sale,minimum_gpm,reasonable_gpm,challenge_gpm from hrm_jh_fendian where targetDate = '"+json.getString("date")+"')TABc " + 
        		"ON TABa.shopcode = TABc.shopcode " + 
        		"LEFT JOIN " + 
        		"(SELECT sDirectorNO,minimum_sale,reasonable_sale,challenge_sale,minimum_gpm,reasonable_gpm,challenge_gpm from hrm_jh_zongjian where targetDate = '"+json.getString("date")+"')TABd " + 
        		"ON TABa.sDirectorNO = TABd.sDirectorNO "+
        		"LEFT JOIN "+
        		"( select sDirectorNO,branch,count(shopCode)num from hrm_jh_dudao GROUP BY sDirectorNO,branch)TABe on TABa.sDirectorNO = TABe.sDirectorNO and TABa.branch = TABe.branch " + 
        		"LEFT JOIN "+
        		"( select shopCode,count(shopCode)shopsum from hrm_jh_dudao group by shopCode)TABf ON TABa.shopcode = TABf.shopcode  "+
        		") GROUP BY " + (json.getString("type").equals("shopCode")?"shopCode,sEmployeeNO":json.getString("type"))+",branch"+
        		")a where 1=1 "+searString+
        		 "group by "+ (json.getString("type").equals("shopCode")?"shopCode,sEmployeeNO":json.getString("type"))+",branch order by "+(json.getString("type").equals("sDirectorNO")?"sOperCenterNO":json.getString("type"))+",branch";
        String headField = "*";
		//参数的json
		JSONObject parmeterJson = DBTools2.getWhereFieldByParameter(json);
		//普通查询的条件及字段值json
		SearchResult  searchResult = new SearchResult();//DBTools2.getWhereFieldBySearch(json);
		//合并所有的结果值统一到一个json中
		JSONObject mergeJson = new JSONObject();//DBTools2.mergeAll(parmeterJson, searchResult);
		String asc = null;
		String ascField = null;
		sql = DBTools2.getSelectPageSql(headField,selectSB,page,limit,ascField,asc,searchResult.getSearchWhere());
        try {
			String parameterArr [] = DBTools2.parsePermeterBySql(sql.getSourceSql(), mergeJson);
			PageResult result = DBTools2.selectByCount(sql,parameterArr);
			JSONArray data = (JSONArray)result.getData();
			response.put(ResponseJson.count, result.getCount());
			response.put(ResponseJson.data, data);
			response.put(ResponseJson.status, ResponseJson.status_Success);
        } catch (ConnectionException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_DB_Connect_Failed);
        } catch (DBClosedException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
            response.put(ResponseJson.msg, ResponseJson.msg_DB_Closed_Failed);
        } catch (SQLException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Query_Data_Error);
        }
		return response;
	}
	@Override
	public JSONObject getBody(JSONObject json, JSONObject response) {
		int limit = json.getIntValue(PageResult.limit);
        int page = json.getIntValue(PageResult.page);
		SqlLang sql = null;
		String shopcodeString="";
		String shopnameString="";
		String sDirectorNameString="";
		String sZoneMagNameString="";
		String sEmployeeNameString="";
		if(json.getString("shopcode")!= null && !json.getString("shopcode").equals("")) {
			shopcodeString = " and shopCode = '"+json.getString("shopcode")+"'";
		}
		if(json.getString("shopname")!= null && !json.getString("shopname").equals("")) {
			shopnameString = " and shopName like '%"+json.getString("shopname")+"%'";
		}
		if(json.getString("sDirectorName")!= null && !json.getString("sDirectorName").equals("")) {
			sDirectorNameString = " and sDirectorName like '%"+json.getString("sDirectorName")+"%'";
		}
		if(json.getString("sZoneMagName")!= null && !json.getString("sZoneMagName").equals("")) {
			sZoneMagNameString = " and sZoneMagName like '%"+json.getString("sZoneMagName")+"%'";
		}
		if(json.getString("sEmployeeName")!= null && !json.getString("sEmployeeName").equals("")) {
			sEmployeeNameString = " and sEmployeeName like '%"+json.getString("sEmployeeName")+"%'";
		}
		String selectSB = " select a.*,b.type  from hrm_jh_performance_detail a left join hrm_jh_performance b on a.NO = b.NO and b.flag=1 where b.type = '"+json.getString("group")+"' and a.date = '"+json.getString("date")+"' "+shopcodeString+shopnameString+sDirectorNameString+sZoneMagNameString+sEmployeeNameString+
				 "group by "+json.getString("group")+",branch  order by "+(json.getString("group").equals("shopCode")?"shopCode":"a.sOperCenterNO")+",branch";
		System.err.println("selectSB>>>"+selectSB);
        String headField = "*";
		//参数的json
		JSONObject parmeterJson = DBTools2.getWhereFieldByParameter(json);
		//普通查询的条件及字段值json
		SearchResult  searchResult = new SearchResult();//DBTools2.getWhereFieldBySearch(json);
		//合并所有的结果值统一到一个json中
		JSONObject mergeJson = new JSONObject();//DBTools2.mergeAll(parmeterJson, searchResult);
		String asc = null;
		String ascField = null;
		sql = DBTools2.getSelectPageSql(headField,selectSB,page,limit,ascField,asc,searchResult.getSearchWhere());
        try {
			String parameterArr [] = DBTools2.parsePermeterBySql(sql.getSourceSql(), mergeJson);
			PageResult result = DBTools2.selectByCount(sql,parameterArr);
			JSONArray data = (JSONArray)result.getData();
			response.put(ResponseJson.count, result.getCount());
			response.put(ResponseJson.data, data);
			response.put(ResponseJson.status, ResponseJson.status_Success);
        } catch (ConnectionException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_DB_Connect_Failed);
        } catch (DBClosedException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
            response.put(ResponseJson.msg, ResponseJson.msg_DB_Closed_Failed);
        } catch (SQLException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Query_Data_Error);
        }
		return response;
	}
	@Override
	public JSONObject getsearch(JSONObject json, JSONObject response) {
		int limit = json.getIntValue(PageResult.limit);
        int page = json.getIntValue(PageResult.page);
		SqlLang sql = null;
		String searString="";
		if(json.getString("val")!= null && !json.getString("val").equals("")) {
			searString = " and sOperCenterNO = '"+json.getString("val")+"'";
		}
        String selectSB = " select *  from hrm_jh_performance_detail where NO = '"+json.getString("NO")+"'  "+searString+ "order by "+json.getString("type")+",branch";
        String headField = "*";
		//参数的json
		JSONObject parmeterJson = DBTools2.getWhereFieldByParameter(json);
		//普通查询的条件及字段值json
		SearchResult  searchResult = new SearchResult();//DBTools2.getWhereFieldBySearch(json);
		//合并所有的结果值统一到一个json中
		JSONObject mergeJson = new JSONObject();//DBTools2.mergeAll(parmeterJson, searchResult);
		String asc = null;
		String ascField = null;
		sql = DBTools2.getSelectPageSql(headField,selectSB,page,limit,ascField,asc,searchResult.getSearchWhere());
        try {
			String parameterArr [] = DBTools2.parsePermeterBySql(sql.getSourceSql(), mergeJson);
			PageResult result = DBTools2.selectByCount(sql,parameterArr);
			JSONArray data = (JSONArray)result.getData();
			response.put(ResponseJson.count, result.getCount());
			response.put(ResponseJson.data, data);
			response.put(ResponseJson.status, ResponseJson.status_Success);
        } catch (ConnectionException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_DB_Connect_Failed);
        } catch (DBClosedException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
            response.put(ResponseJson.msg, ResponseJson.msg_DB_Closed_Failed);
        } catch (SQLException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Query_Data_Error);
        }
		return response;
	}
	@Override
	public JSONObject add(JSONObject json, JSONObject response,HttpSession session) {  
	
    	// 先查询主键是否存在
    	try {
	    	String keywords = "NO";
	    	String insertTable = "hrm_jh_performance";
	    	String insertField = "NO,name,date,type,val,flag,lrr,lrsj=now()";
			JSONObject existJson = DBTools2.getIsExist(keywords,insertTable, json);
			if(existJson != null){
				response.put(ResponseJson.msg, "已存在该数据");
				response.put(ResponseJson.status, ResponseJson.status_Failed);
				return response;
			}
			JSONObject userJson = (JSONObject)session.getAttribute(SessionJson.currentUser);
			JSONObject infoJson = json.getJSONObject("performance");
			infoJson.put(Dict_infoEntity.lrr, userJson.getString(UserJson.userId));
			
			LinkedList<SQLBean> beanList = new LinkedList<SQLBean>();
			String infoSql = DBTools.getInsertSql(insertField, insertTable);
			String[] infoParam = DBTools.getInsertParms(insertField, infoJson);
			SQLBean infoBean = new SQLBean(infoSql, infoParam);
			beanList.add(infoBean);
			//结果录入
			JSONArray insertArray = json.getJSONArray("detail");
			if(insertArray.size() > 0){
				String insertArrayField = "NO='"+infoJson.getString("NO")+"',date='"+infoJson.getString("date")+"',shopCode,shopName,branch,sOperCenterNO,sOperCenter,sDirectorNO,sDirectorName,sZoneMagNO,sZoneMagName,sEmployeeNO,sEmployeeName,"
						+ "days,totalsalemoney,totalinmoney,GPM,minimum_sale,reasonable_sale,challenge_sale,AVG,minimum_percent,reasonable_percent,challenge_percent,"
						+ "minimum_gpm,reasonable_gpm,challenge_gpm,GPM_percent,minimum_gpm_percent,reasonable_gpm_percent,challenge_gpm_percent,sD_minimum_sale,sD_reasonable_sale,"
						+ "sD_challenge_sale,sD_minimum_percent,sD_reasonable_percent,sD_challenge_percent";
				String arrayInsertSql = DBTools.getInsertSql(insertArrayField, "hrm_jh_performance_detail");
				List<String[]> arrayInsertParams = DBTools.getInsertListParms(insertArrayField, insertArray);	
				SQLBean insertArrayBean = new SQLBean(arrayInsertSql, arrayInsertParams);
				beanList.add(insertArrayBean);
			}
			DBTools.executeBatch(beanList);
			response.put(ResponseJson.status, ResponseJson.status_Success);
			response.put(ResponseJson.msg, ResponseJson.msg_Add_Data_Success);
		}  catch (ConnectionException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
    		response.put(ResponseJson.msg, ResponseJson.msg_No_Porwer_Query_Field);
		} catch (DBClosedException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
    		response.put(ResponseJson.msg, ResponseJson.msg_DB_Closed_Failed);
		} catch (SQLException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Add_Data_Error);
		}
		return response;
		
	}
	@Override
	public JSONObject getperformance(JSONObject json, JSONObject response) {
		JSONObject searchJson = json.getJSONObject("search");
		int limit = json.getIntValue(PageResult.limit);
        int page = json.getIntValue(PageResult.page);
		SqlLang sql = null;
		String nameString="";
		String typeString="";
		if(searchJson.getString("name")!= null && !searchJson.getString("name").equals("")){
			nameString= " and name like '%"+searchJson.getString("name")+"%'";
		}
		if(searchJson.getString("type")!= null && !searchJson.getString("type").equals("")){
			typeString= " and type = '"+searchJson.getString("type")+"'";
		}
        String selectSB = "select NO,name,date,type,val,flag,(select name from t_user where userId = a.lrr)lrr,lrsj,"
        		+ "(select name from t_user where userId = a.shr)shr,shsj,(select name from t_user where userId = a.zfr)zfr,zfsj from hrm_jh_performance a "
        		+ "where date = '"+searchJson.getString("date")+"'"+nameString+typeString;
        String headField = "*";
		//参数的json
		JSONObject parmeterJson = DBTools2.getWhereFieldByParameter(json);
		//普通查询的条件及字段值json
		SearchResult  searchResult = new SearchResult();//DBTools2.getWhereFieldBySearch(json);
		//合并所有的结果值统一到一个json中
		JSONObject mergeJson = new JSONObject();//DBTools2.mergeAll(parmeterJson, searchResult);
		String asc = null;
		String ascField = null;
		sql = DBTools2.getSelectPageSql(headField,selectSB,page,limit,ascField,asc,searchResult.getSearchWhere());
        try {
			String parameterArr [] = DBTools2.parsePermeterBySql(sql.getSourceSql(), mergeJson);
			PageResult result = DBTools2.selectByCount(sql,parameterArr);
			JSONArray data = (JSONArray)result.getData();
			response.put(ResponseJson.count, result.getCount());
			response.put(ResponseJson.data, data);
			response.put(ResponseJson.status, ResponseJson.status_Success);
        } catch (ConnectionException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_DB_Connect_Failed);
        } catch (DBClosedException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
            response.put(ResponseJson.msg, ResponseJson.msg_DB_Closed_Failed);
        } catch (SQLException e) {
            response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Query_Data_Error);
        }
		return response;
	}
	@Override
	public JSONObject batchdel(JSONObject json, JSONObject response) {
		String whereField = "NO";
    	String delTable = "hrm_jh_performance";
    	try {
			DBTools2.delete(whereField, delTable,json);
			DBTools2.delete(whereField, "hrm_jh_performance_detail",json);
			response.put(ResponseJson.status, ResponseJson.status_Success);
			response.put(ResponseJson.msg, ResponseJson.msg_Del_Data_Success);
		}  catch (ConnectionException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
    		response.put(ResponseJson.msg, ResponseJson.msg_No_Porwer_Query_Field);
		} catch (DBClosedException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
    		response.put(ResponseJson.msg, ResponseJson.msg_DB_Closed_Failed);
		} catch (SQLException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Del_Data_Error);
		}
		return response;
	}
	@Override
	public JSONObject shenhe(JSONObject json, JSONObject response) {
		JSONArray json1=json.getJSONArray("shenheData");
		int c = json.getJSONArray("shenheData").size();    
		String whereField = "NO";
    	String updateTable = "hrm_jh_performance";
    	String updateField = "flag,shr,shsj=now()";
    	try { 
    		DBTools2.updateBatch(updateField, whereField, updateTable, json1);
			response.put(ResponseJson.status, ResponseJson.status_Success);
			response.put(ResponseJson.msg, ResponseJson.msg_finish_Data_Success);
		}  catch (ConnectionException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
    		response.put(ResponseJson.msg, ResponseJson.msg_No_Porwer_Query_Field);
		} catch (DBClosedException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
    		response.put(ResponseJson.msg, ResponseJson.msg_DB_Closed_Failed);
		} catch (SQLException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Del_Data_Error);
		}
		return response;  
	}
	@Override
	public JSONObject zuofei(JSONObject json, JSONObject response) {
		JSONArray json1=json.getJSONArray("zuofeiData");
		int c = json.getJSONArray("zuofeiData").size();    
		String whereField = "NO";
    	String updateTable = "hrm_jh_performance";
    	String updateField = "flag,zfr,zfsj=now()";
    	try { 
    		DBTools2.updateBatch(updateField, whereField, updateTable, json1);
			response.put(ResponseJson.status, ResponseJson.status_Success);
			response.put(ResponseJson.msg, ResponseJson.msg_finish_Data_Success);
		}  catch (ConnectionException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
    		response.put(ResponseJson.msg, ResponseJson.msg_No_Porwer_Query_Field);
		} catch (DBClosedException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
    		response.put(ResponseJson.msg, ResponseJson.msg_DB_Closed_Failed);
		} catch (SQLException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Del_Data_Error);
		}
		return response;  
	}
	@Override
	public JSONObject dudao_exportExcel(HttpServletRequest request, HttpServletResponse responseServlet,JSONObject response) {
		JSONObject json = JSONObject.parseObject(request.getParameter("search"));
		SqlLang sql = null;
		String shopcodeString="";
		String shopnameString="";
		String sDirectorNameString="";
		String sZoneMagNameString="";
		String sEmployeeNameString="";
		if(json.getString("shopcode")!= null && !json.getString("shopcode").equals("")) {
			shopcodeString = " and shopCode = '"+json.getString("shopcode")+"'";
		}
		if(json.getString("shopname")!= null && !json.getString("shopname").equals("")) {
			shopnameString = " and shopName like '%"+json.getString("shopname")+"%'";
		}
		if(json.getString("sDirectorName")!= null && !json.getString("sDirectorName").equals("")) {
			sDirectorNameString = " and sDirectorName like '%"+json.getString("sDirectorName")+"%'";
		}
		if(json.getString("sZoneMagName")!= null && !json.getString("sZoneMagName").equals("")) {
			sZoneMagNameString = " and sZoneMagName like '%"+json.getString("sZoneMagName")+"%'";
		}
		if(json.getString("sEmployeeName")!= null && !json.getString("sEmployeeName").equals("")) {
			sEmployeeNameString = " and sEmployeeName like '%"+json.getString("sEmployeeName")+"%'";
		}
		String selectSB = " select a.*,b.type  from hrm_jh_performance_detail a left join hrm_jh_performance b on a.NO = b.NO and b.flag=1 where b.type = '"+json.getString("group")+"' and a.date = '"+json.getString("date")+"' "+shopcodeString+shopnameString+sDirectorNameString+sZoneMagNameString+sEmployeeNameString+
				 "group by "+json.getString("group")+",branch  order by shopCode,branch";
        String format =  request.getParameter(PageResult.format);
		String tableTitle ="店号,店名,门店性质,中心,总监,区总,督导,销售天数,含税销售额,含税销售成本,销售毛利,日均销售保底目标,日均销售合理目标,日均销售挑战目标,单店实际日均销售,日均销售保底达成比,日均销售合理达成比,日均销售挑战达成比,毛利率保底目标,毛利率合理目标,毛利率挑战目标,毛利率实际值,毛利率保底达成比,毛利率合理达成比,毛利率挑战达成比";
		String tableField ="shopCode,shopName,branch,sOperCenter,sDirectorName,sZoneMagName,sEmployeeName,days,totalsalemoney,totalinmoney,GPM,minimum_sale,reasonable_sale,challenge_sale,AVG,minimum_percent,reasonable_percent,challenge_percent,minimum_gpm,reasonable_gpm,challenge_gpm,GPM_percent,minimum_gpm_percent,reasonable_gpm_percent,challenge_gpm_percent";
		String [] tableTitles = tableTitle.split(DefaultConfiguration.separator_douhao);
		String [] tableFields = tableField.split(DefaultConfiguration.separator_douhao);
		OutputStream out = null;
		try {
			JSONArray arr = DBTools2.selectArr(selectSB);
			PageResult.formatType type = PageResult.formatType.valueOf(format);
			String fileName = "";
			switch(type){
				case excel:
					fileName = "督导绩效查询.xls";
					break;
				case csv:
					fileName = "督导绩效查询.csv";
					break;
				case pdf:
					break;
				case word:
					break;
			}
			fileName = FileUtils.encodeFilename(fileName, request);
			String headStr = "attachment; filename=\"" + fileName + "\"";
			responseServlet.setHeader("content-Type", "application/vnd.ms-excel");
			responseServlet.setHeader("Content-Disposition", headStr);
			out = responseServlet.getOutputStream();
			ExportExcel ex = new ExportExcel("督导绩效查询", tableTitles,tableFields, arr);
			ex.export(out);
			out.flush();
			out.close();
			response.put(ResponseJson.status, 1002);
			response.put(ResponseJson.msg, "删除成功");
		} catch (ConnectionException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_DB_Connect_Failed);
		} catch (DBClosedException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_DB_Closed_Failed);
		} catch (SQLException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Query_Data_Error);
		} catch (Exception e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Export_Data_Error);
		}
		return response;
	}
	@Override
	public JSONObject feidudao_exportExcel(HttpServletRequest request, HttpServletResponse responseServlet,JSONObject response) {
		JSONObject json = JSONObject.parseObject(request.getParameter("search"));
		SqlLang sql = null;
		String shopcodeString="";
		String shopnameString="";
		String sDirectorNameString="";
		String sZoneMagNameString="";
		String sEmployeeNameString="";
		if(json.getString("shopcode")!= null && !json.getString("shopcode").equals("")) {
			shopcodeString = " and shopCode = '"+json.getString("shopcode")+"'";
		}
		if(json.getString("shopname")!= null && !json.getString("shopname").equals("")) {
			shopnameString = " and shopName like '%"+json.getString("shopname")+"%'";
		}
		if(json.getString("sDirectorName")!= null && !json.getString("sDirectorName").equals("")) {
			sDirectorNameString = " and sDirectorName like '%"+json.getString("sDirectorName")+"%'";
		}
		if(json.getString("sZoneMagName")!= null && !json.getString("sZoneMagName").equals("")) {
			sZoneMagNameString = " and sZoneMagName like '%"+json.getString("sZoneMagName")+"%'";
		}
		if(json.getString("sEmployeeName")!= null && !json.getString("sEmployeeName").equals("")) {
			sEmployeeNameString = " and sEmployeeName like '%"+json.getString("sEmployeeName")+"%'";
		}
		String selectSB = " select a.*,b.type  from hrm_jh_performance_detail a left join hrm_jh_performance b on a.NO = b.NO and b.flag=1 where b.type = '"+json.getString("group")+"' and a.date = '"+json.getString("date")+"' "+shopcodeString+shopnameString+sDirectorNameString+sZoneMagNameString+sEmployeeNameString+
				 "group by "+json.getString("group")+",branch  order by a.sOperCenterNO,branch";
        String format =  request.getParameter(PageResult.format);
		String tableTitle ="中心,总监,门店性质,含税销售金额,含税销售成本,含税销售毛利,月销售保底目标,月销售售合理目标,月销售售挑战目标,月销售保底达成比,月销售合理达成比,月销售挑战达成比,毛利率保底目标,毛利率合理目标,毛利率挑战目标,毛利率实际值,毛利率保底达成比,毛利率合理达成比,毛利率挑战达成比";
		String tableField ="sOperCenter,sDirectorName,branch,totalsalemoney,totalinmoney,GPM,sD_minimum_sale,sD_reasonable_sale,sD_challenge_sale,sD_minimum_percent,sD_reasonable_percent,sD_challenge_percent,minimum_gpm,reasonable_gpm,challenge_gpm,GPM_percent,minimum_gpm_percent,reasonable_gpm_percent,challenge_gpm_percent";
		String [] tableTitles = tableTitle.split(DefaultConfiguration.separator_douhao);
		String [] tableFields = tableField.split(DefaultConfiguration.separator_douhao);
		OutputStream out = null;
		try {
			JSONArray arr = DBTools2.selectArr(selectSB);
			PageResult.formatType type = PageResult.formatType.valueOf(format);
			String fileName = "";
			switch(type){
				case excel:
					fileName = "非督导绩效查询.xls";
					break;
				case csv:
					fileName = "非督导绩效查询.csv";
					break;
				case pdf:
					break;
				case word:
					break;
			}
			fileName = FileUtils.encodeFilename(fileName, request);
			String headStr = "attachment; filename=\"" + fileName + "\"";
			responseServlet.setHeader("content-Type", "application/vnd.ms-excel");
			responseServlet.setHeader("Content-Disposition", headStr);
			out = responseServlet.getOutputStream();
			ExportExcel ex = new ExportExcel("非督导绩效查询", tableTitles,tableFields, arr);
			ex.export(out);
			out.flush();
			out.close();
			response.put(ResponseJson.status, 1002);
			response.put(ResponseJson.msg, "删除成功");
		} catch (ConnectionException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_DB_Connect_Failed);
		} catch (DBClosedException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_DB_Closed_Failed);
		} catch (SQLException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Query_Data_Error);
		} catch (Exception e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Export_Data_Error);
		}
		return response;
	}
	@Override
	public JSONObject exportExcel(HttpServletRequest request, HttpServletResponse responseServlet,JSONObject response) {
		JSONObject json = JSONObject.parseObject(request.getParameter("search"));
		SqlLang sql = null;
		String searString="";
		if(json.getString("val")!= null && !json.getString("val").equals("")) {
			searString = " and sOperCenterNO = '"+json.getString("val")+"'";
		}
        String selectSB = "select id,NO,shopCode,shopName,IF(branch=0,'老店','新店')as branch,date,sOperCenter,sDirectorName,sZoneMagName,sEmployeeName,"
        		+ "days,totalsalemoney,totalinmoney,GPM,minimum_sale,reasonable_sale,challenge_sale,AVG,CONCAT(minimum_percent,'%')minimum_percent,"
        		+ "CONCAT(reasonable_percent,'%')reasonable_percent,CONCAT(challenge_percent,'%')as challenge_percent,"
        		+ "CONCAT(minimum_gpm,'%')minimum_gpm,CONCAT(reasonable_gpm,'%')reasonable_gpm,CONCAT(challenge_gpm,'%')challenge_gpm,"
        		+ "CONCAT(GPM_percent,'%')GPM_percent,CONCAT(minimum_gpm_percent,'%')minimum_gpm_percent,CONCAT(reasonable_gpm_percent,'%')reasonable_gpm_percent,"
        		+ "CONCAT(challenge_gpm_percent,'%')challenge_gpm_percent,sD_minimum_sale,sD_reasonable_sale,sD_challenge_sale,"
        		+ "CONCAT(sD_minimum_percent,'%')sD_minimum_percent,CONCAT(sD_reasonable_percent,'%')sD_reasonable_percent,CONCAT(sD_challenge_percent,'%')sD_challenge_percent "
        		+ "from hrm_jh_performance_detail where NO = '"+json.getString("NO")+"'"+searString+ "order by "+json.getString("type")+",branch";
        System.err.println(">"+selectSB);
        String format =  request.getParameter(PageResult.format);
        
		String tableTitle ="中心,总监,门店性质,含税销售金额,含税销售成本,含税销售毛利,月销售保底目标,月销售售合理目标,月销售售挑战目标,月销售保底达成比,月销售合理达成比,月销售挑战达成比,毛利率保底目标,毛利率合理目标,毛利率挑战目标,毛利率实际值,毛利率保底达成比,毛利率合理达成比,毛利率挑战达成比";
		String tableField ="sOperCenter,sDirectorName,branch,totalsalemoney,totalinmoney,GPM,sD_minimum_sale,sD_reasonable_sale,sD_challenge_sale,sD_minimum_percent,sD_reasonable_percent,sD_challenge_percent,minimum_gpm,reasonable_gpm,challenge_gpm,GPM_percent,minimum_gpm_percent,reasonable_gpm_percent,challenge_gpm_percent";
		
		if(json.getString("type").equals("shopCode")) {
			tableTitle ="店号,店名,门店性质,中心,总监,区总,督导,销售天数,含税销售额,含税销售成本,销售毛利,日均销售保底目标,日均销售合理目标,日均销售挑战目标,单店实际日均销售,日均销售保底达成比,日均销售合理达成比,日均销售挑战达成比,毛利率保底目标,毛利率合理目标,毛利率挑战目标,毛利率实际值,毛利率保底达成比,毛利率合理达成比,毛利率挑战达成比";
			tableField ="shopCode,shopName,branch,sOperCenter,sDirectorName,sZoneMagName,sEmployeeName,days,totalsalemoney,totalinmoney,GPM,minimum_sale,reasonable_sale,challenge_sale,AVG,minimum_percent,reasonable_percent,challenge_percent,minimum_gpm,reasonable_gpm,challenge_gpm,GPM_percent,minimum_gpm_percent,reasonable_gpm_percent,challenge_gpm_percent";
			
		}

		
		
		String [] tableTitles = tableTitle.split(DefaultConfiguration.separator_douhao);
		String [] tableFields = tableField.split(DefaultConfiguration.separator_douhao);
		OutputStream out = null;
		try {
			JSONArray arr = DBTools2.selectArr(selectSB);
			PageResult.formatType type = PageResult.formatType.valueOf(format);
			String fileName = "";
			switch(type){
				case excel:
					fileName = "绩效查询.xls";
					break;
				case csv:
					fileName = "绩效查询.csv";
					break;
				case pdf:
					break;
				case word:
					break;
			}
			fileName = FileUtils.encodeFilename(fileName, request);
			String headStr = "attachment; filename=\"" + fileName + "\"";
			responseServlet.setHeader("content-Type", "application/vnd.ms-excel");
			responseServlet.setHeader("Content-Disposition", headStr);
			out = responseServlet.getOutputStream();
			ExportExcel ex = new ExportExcel("绩效查询", tableTitles,tableFields, arr);
			ex.export(out);
			out.flush();
			out.close();
			response.put(ResponseJson.status, 1002);
			response.put(ResponseJson.msg, "删除成功");
		} catch (ConnectionException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_DB_Connect_Failed);
		} catch (DBClosedException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_DB_Closed_Failed);
		} catch (SQLException e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Query_Data_Error);
		} catch (Exception e) {
			response.put(ResponseJson.status, ResponseJson.status_Failed);
			response.put(ResponseJson.msg, ResponseJson.msg_Export_Data_Error);
		}
		return response;
	}
}
